package core;

import core.handler.BeanRowMapper;
import core.handler.ColumnMapRowMapper;
import core.handler.RowMapper;
import core.handler.SingleColumnRowMapper;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
 * 数据库操作工具类
 * 仿Spring JdbcTemplate RowMapper
 *
 * @author Feny
 * @date Created in 2022/4/26
 */
public class Db {
    private static String url = "";
    private static String username = "";
    private static String password = "";

    static {
        Properties properties = new Properties();
        try (InputStream inputStream = ClassLoader
                .getSystemResourceAsStream("jdbc.properties")) {
            properties.load(inputStream);

            String driver = properties.getProperty("driver").trim();
            url = properties.getProperty("url").trim();
            username = properties.getProperty("username").trim();
            password = properties.getProperty("password").trim();

            Class.forName(driver);
        } catch (IOException e) {
            System.out.println("读取文件失败");
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("加载mysql驱动失败！！");
            e.printStackTrace();
        }
    }

    /**
     * 得到数据连接
     *
     * @return 数据库链接
     */
    private static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            System.out.println("连接数据库失败！！");
        }
        return null;
    }

    /**
     * 关闭数据库连接
     */
    private static void close(Object... objsToClose) {
        for (Object obj : objsToClose) {
            if (null != obj) {
                if (obj instanceof AutoCloseable) {
                    try {
                        ((AutoCloseable) obj).close();
                    } catch (Exception e) {
                        System.out.println(e.getMessage());
                    }
                }
            }
        }
    }

    /**
     * 统一数据绑定方法
     *
     * @param sql    执行sql
     * @param params 参数值
     * @return PreparedStatement
     */
    private static PreparedStatement createStatement(Connection conn, String sql,
                                                     Object... params) throws SQLException {
        PreparedStatement statement = conn.prepareStatement(sql);
        return fillParams(statement, params);
    }

    /**
     * 填充SQL的参数
     *
     * @param statement PreparedStatement
     * @param params    SQL的参数
     * @return java.sql.PreparedStatement
     * @author Feny
     * @date 2022/4/28
     */
    private static PreparedStatement fillParams(PreparedStatement statement,
                                                Object... params) throws SQLException {
        if (null == params) {
            return statement;
        }
        //第一个参数从1计数
        int paramIndex = 1;
        for (Object param : params) {
            statement.setObject(paramIndex++, param);
        }
        return statement;
    }

    /**
     * 增删改(insert,delete,update)
     *
     * @param sql    执行sql
     * @param params 参数值
     * @return 成功记录数
     */
    public static int executeUpdate(String sql, Object... params) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        try {
            ps = createStatement(Objects.requireNonNull(conn), sql, params);
            return ps.executeUpdate();
        } finally {
            close(ps, conn);
        }
    }

    /**
     * 查询一条记录，返回list集合
     *
     * @param sql    执行sql
     * @param params 参数值
     * @return 返回List<Map < String, Object>>
     */
    public static List<Map<String, Object>> queryForListBean(String sql, Object[] params) throws Exception {
        return query(sql, params, new ColumnMapRowMapper());
    }

    /**
     * 查询一条记录，返回map集合
     *
     * @param sql    执行sql
     * @param params 参数值
     * @return 返回Map<String, Object>结果
     */
    public static Map<String, Object> queryForMap(String sql, Object[] params) throws Exception {
        List<Map<String, Object>> result = queryForListBean(sql, params);
        if (result.size() != 0 && result.size() > 1) {
            throw new SQLException(String.format("预期获取一行记录，实际有%s行：", result.size()));
        }
        return result.get(0);
    }

    public static <T> T queryForBean(String sql, Object[] params,
                                     Class<T> elementType) throws Exception {
        List<T> results = queryForListBean(sql, params, elementType);
        return getOne(results);
    }

    /**
     * 查询单列结果
     *
     * @param sql         查询语句
     * @param params      参数
     * @param elementType 数据类型
     * @return java.util.List<T> 返回结果
     * @author Feny
     * @date 2022/7/12
     */
    public static <T> T queryForObject(String sql, Object[] params,
                                       Class<T> elementType) throws Exception {
        List<T> results = queryForList(sql, params, elementType);
        return getOne(results);
    }

    /**
     * 查询结果为实体
     *
     * @param sql         查询语句
     * @param params      参数
     * @param elementType 数据类型
     * @return java.util.List<T> 返回结果
     * @author Feny
     * @date 2022/7/12
     */
    public static <T> List<T> queryForListBean(String sql, Object[] params,
                                               Class<T> elementType) throws Exception {
        return query(sql, params, new BeanRowMapper<>(elementType));
    }

    /**
     * 查询单列结果
     *
     * @param sql         查询语句
     * @param params      参数
     * @param elementType 数据类型
     * @return java.util.List<T> 返回结果
     * @author Feny
     * @date 2022/7/12
     */
    public static <T> List<T> queryForList(String sql, Object[] params,
                                           Class<T> elementType) throws Exception {
        return query(sql, params, new SingleColumnRowMapper<>(elementType));
    }

    /**
     * @param sql       执行sql
     * @param params    参数值
     * @param rowMapper 结果集处理
     * @return 返回处理后的结果集
     * @author Feny
     * @date 2022/4/26
     */
    public static <T> List<T> query(String sql, Object[] params,
                                    RowMapper<T> rowMapper) throws Exception {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        try {
            ps = createStatement(Objects.requireNonNull(conn), sql, params);
            return executeQuery(ps, rowMapper);
        } finally {
            close(ps, conn);
        }
    }

    private static <T> List<T> executeQuery(PreparedStatement ps, RowMapper<T> rowMapper) throws Exception {
        ResultSet rs = null;
        try {
            rs = ps.executeQuery();
            List<T> result = new ArrayList<>();
            while (rs.next()) {
                T row = rowMapper.mapRow(rs);
                result.add(row);
            }
            return result;
        } finally {
            close(rs);
        }
    }

    private static <T> T getOne(List<T> results) {
        if (results.isEmpty()) {
            return null;
        }

        if (results.size() > 1) {
            throw new RuntimeException(String.format("存在%s条记录数", results.size()));
        }
        return results.iterator().next();
    }
}
